<?php
include_once('Sqlexecutor.php');
class TileUserInfo
{
    private $_dbh;
    private $_logger;

    public function __construct($dbh, $logger = Null)
    {/*{{{*/
        $this->_dbh = $dbh; 
        $this->_logger = $logger;
    }/*}}}*/

    public function getAllList($usrid)
    {/*{{{*/
        $sql = "select `price`, `score`, `tile_id` from `bs_tile_price_score` where `usrid` = ?";
        $val[] = $usrid;
        try
        {
            $list = $this->_dbh->querys($sql, $val);
            return $list;
        }
        catch(Exception $e)
        {
            if($this->_logger)
                $this->_logger->error($e->getFile() . ":" . $e->getLine() . "," . $e->getMessage());
            return false;
        }
    }/*}}}*/

    public function getListBySeriesID($usrid, $series_id)
    {/*{{{*/
        $sql = "select `price`, `score`, `tile_id` from `bs_tile_price_score` where `usrid` = ? and `series_id` = ?";
        $val[] = $usrid;
        $val[] = $series_id;
        try
        {
            $list = $this->_dbh->querys($sql, $val);
            return $list;
        }
        catch(Exception $e)
        {
            if($this->_logger)
                $this->_logger->error($e->getFile() . ":" . $e->getLine() . "," . $e->getMessage());
            return false;
        }
    }/*}}}*/

    public function getListOfAvgPriceBySeriesID($series_id = NULL)
    {/*{{{*/
        if($id === NULL)
        {
            $sql = "select avg(`price`) as avg_price, avg(`score`) as avg_score, `tile_id` from `bs_tile_price_score` group by `tile_id`";
            $val = array();
        }
        else
        {
            $sql = "select avg(`price`) as avg_price, avg(`score`) as avg_score, `tile_id` from `bs_tile_price_score` where `series_id` = ? group by `tile_id`";
            $val[] = $series_id;
        }
        try
        {
            $list = $this->_dbh->querys($sql, $val);
            return $list;
        }
        catch(Exception $e)
        {
            if($this->_logger)
                $this->_logger->error($e->getFile() . ":" . $e->getLine() . "," . $e->getMessage());
            return false;
        }
    }/*}}}*/

    public function getInfoByTileID($usrid, $tile_id)
    {/*{{{*/
        $sql = "select `price`, `score`, `series_id` from `bs_tile_price_score` where `usrid` = ? and `tile_id` = ?";
        $val[] = $usrid;
        $val[] = $tile_id;
        try
        {
            $info = $this->_dbh->query($sql, $val);
            if($info === false)
                $info = NULL;
            return $info;
        }
        catch(Exception $e)
        {
            if($this->_logger)
                $this->_logger->error($e->getFile() . ":" . $e->getLine() . "," . $e->getMessage());
            return false;
        }
    }/*}}}*/

    public function createInfo($usrid, $tile_id, $series_id, $price, $score)
    {/*{{{*/
        $sql = "insert into `bs_tile_price_score` set `usrid` = ?, `tile_id` = ?, `series_id` = ?, `price` = ?, `score` = ?";
        $val[] = $usrid;
        $val[] = $tile_id;
        $val[] = $series_id;
        $val[] = $price;
        $val[] = $score;
        try
        {
            $this->_dbh->beginTrans();
            $this->_dbh->execute($sql, $val); 
            $lastId = $this->_dbh->getLastInsertID();
            $this->_dbh->commit();

            return $lastId;
        }
        catch(Exception $e)
        {
            $this->_dbh->rollback();
            if($this->_logger)
                $this->_logger->error($e->getFile() . ":" . $e->getLine() . "," . $e->getMessage());
            if($e->errorInfo[1] == 1062 && $e->errorInfo[0] == "23000")
            {
                // Duplicate key
                return -1;
            }
            else
            {
                // Unknow error
                return -100;
            }
        }
    }/*}}}*/

    public function updateByTileID($usrid, $tile_id, $series_id, $price, $score)
    {/*{{{*/
        $sql = "update `bs_tile_price_score` set `price` = ?, `score` = ?, `series_id` = ?  where `usrid` = ? and `tile_id` = ?";
        $val[] = $price;
        $val[] = $score;
        $val[] = $series_id;
        $val[] = $usrid;
        $val[] = $tile_id;
        try
        {
            $this->_dbh->beginTrans();
            $res = $this->_dbh->execute($sql, $val); 
            $this->_dbh->commit();
            
            // 0: not change, 1 : change 1 rows
            return $res;
        }
        catch(Exception $e)
        {
            $this->_dbh->rollback();
            if($this->_logger)
                $this->_logger->error($e->getFile() . ":" . $e->getLine() . "," . $e->getMessage());
            if($e->errorInfo[1] == 1062 && $e->errorInfo[0] == "23000")
            {
                // Duplicate key
                return -1;
            }
            else
            {
                // Unknow error
                return -100;
            }
        }
    }/*}}}*/

    public function deleteByTileID($usrid, $tile_id)
    {/*{{{*/
        $sql = "delete from `bs_tile_price_score` where `usrid` = ? and `tile_id` = ?";
        $val[] = $usrid;
        $val[] = $tile_id;
        try
        {
            $this->_dbh->beginTrans();
            $res = $this->_dbh->execute($sql, $val); 
            $this->_dbh->commit();
            
            // 0: not delete, 1 : delete 1 rows
            return $res;
        }
        catch(Exception $e)
        {
            $this->_dbh->rollback();
            if($this->_logger)
                $this->_logger->error($e->getFile() . ":" . $e->getLine() . "," . $e->getMessage());
            
            // Unknow error
            return -100;
        }
    }/*}}}*/
}

/* example 
$dbh = new Sqlexecutor('localhost', 'tile', 'tile', 'tile');
$s = new Tile($dbh);
$res = $s->createTile("K203031", 300, 300, "marble", "1/26920ce3876a7f26637ca25d4a026e66.jpg", "1/26920ce3876a7f26637ca25d4a026e66.jpg", 1);
var_dump($res);
$res = $s->createTile("K599318", 300, 300, "marble", "2/082ea4afe96280ab894a50f7b276e679.jpg", "2/082ea4afe96280ab894a50f7b276e679.jpg", 1);
var_dump($res);
$res = $s->getInfoByID(1);
var_dump($res);
$list = $s->getListBySeriesID(1);
var_dump($list);
$res = $s->createTile("K599318", 300, 300, "marble", "2/082ea4afe96280ab894a50f7b276e679.jpg", "2/082ea4afe96280ab894a50f7b276e679.jpg", 1);
var_dump($res);
$res = $s->updateTileByID("K599318", 300, 300, "marble", "2/082ea4afe96280ab894a50f7b276e679.jpg", "2/082ea4afe96280ab894a50f7b276e679.jpg", 1, 2);
var_dump($res);
$res = $s->updateTileByID("K203031", 300, 300, "marble", "2/082ea4afe96280ab894a50f7b276e679.jpg", "2/082ea4afe96280ab894a50f7b276e679.jpg", 1, 2);
var_dump($res);
$res = $s->updateTileByID("K203031", 400, 400, "marble", "2/082ea4afe96280ab894a50f7b276e679.jpg", "2/082ea4afe96280ab894a50f7b276e679.jpg", 1, 1);
var_dump($res);
 */
